
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <Python.h>
#include <string.h>
#include "functions.h"

//creates the name of the database
char* bdName(int ano)
{
	char bd[16] = "";
	strcpy(bd, "");
	strcat(bd, "db/rebides0");
	strcat(bd,PyString_AsString(PyString_FromFormat("%d",ano)));
	strcat(bd,".db");
	return bd;
}

//Function used by qsort to organize the type struct NumberPerYear
int comparenNumberPerYear(struct NumberPerYear *elem1, struct NumberPerYear *elem2)
{
   if ( elem1->number < elem2->number)
      return -1;

   else if (elem1->number > elem2->number)
      return 1;

   else
      return 0;
}

//Function used by qsort to organize type struct nDocentesPerInstPerYear
int compNDocentesPerInstPerYear(struct nDocentesPerInstPerYear *elem1, struct nDocentesPerInstPerYear *elem2)
{
	if ( elem1->nDocentes < elem2->nDocentes)
      return -1;

   else if (elem1->nDocentes > elem2->nDocentes)
      return 1;

   else
      return 0;
}

//Function used by qsort to organize type struct nDocentesPerDegreePerInstPerYear
int compNDocentesPerDegreePerInstPerYear(struct nDocentesPerDegreePerInstPerYear *elem1, struct nDocentesPerDegreePerInstPerYear *elem2)
{
	if ( elem1->nDocentes < elem2->nDocentes)
      return -1;

   else if (elem1->nDocentes > elem2->nDocentes)
      return 1;

   else
      return 0;
}

//Sort an array with PyObjects
int PyStringCmp(PyObject** elem1, PyObject** elem2)
{
	return strcmp(PyString_AsString(*elem1), PyString_AsString(*elem2));
}

//Calllback put in the pointer notused the information from the query to the bd
int callbackInt(void **NotUsed, int argc, char **argv, char **azColName)
{
	*NotUsed = atoi( argv[0] ? argv[0] : "NULL");
	return 0;
}

//Calllback put in the PyList (not used - pointer to Pylist)
//the information from the query to the bd
int callbackPyList(void **NotUsed, int argc, char **argv, char **azColName)
{
	PyList_Append(*NotUsed,  PyString_FromString(argv[0]));//Coloca na Pylist os valores
	return 0;
}

//callback that fill the array with NDocentesPerInstPerYear structs 
//with the information from the query to the bd
int callBack_NDIY(struct nDocentesPerInstPerYear **NotUsed, int argc, char **argv, char **azColName)
{
	//allocates memory for the name of institution
	(*NotUsed)->inst = malloc((strlen(argv[0])+1)*sizeof(char));
	
	strcpy((*NotUsed)->inst,argv[0]);
	(*NotUsed)->nDocentes = atoi(argv[1]);

	//point to the next struct in the array
	(*NotUsed)++;

    return 0;
}

//callback that fill the array with nDocentesPerDegreePerInstPerYear structs 
//with the information from the query to the bd
//NotUsed(Its a Pointer to a Pointer for the first element of the array of strucks)
int callBack_NDDIY(struct nDocentesPerDegreePerInstPerYear **NotUsed, int argc, char **argv, char **azColName)
{
	//allocate memory for name of intitution and degree
	(*NotUsed)->inst = malloc((strlen(argv[0])+1)*sizeof(char));
	(*NotUsed)->degree = malloc((strlen(argv[1])+1)*sizeof(char));

	//copy information for the structure
	strcpy((*NotUsed)->inst,argv[0]);
	strcpy((*NotUsed)->degree,argv[1]);
	(*NotUsed)->nDocentes = atoi(argv[2]);

	//point to the next struct in the array
	(*NotUsed)++;

    return 0;
}

//Open BD, do query and Close BD
void openBD(char bd[], char sql[], funcao fn, void* NotUsed)
{
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	rc = sqlite3_open(bd, &db);
	rc = sqlite3_exec(db, sql, fn, NotUsed, &zErrMsg);
	if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	sqlite3_close(db);
}

//Return a list with the number of instituitions of the years choosen by the user
//used by function nDocentesPerInstPerYear to know how many memory need to allocate
PyObject* nInstYear(PyObject* yearList, PyObject* orderBy)
{
	int i = 0;
	char bd[16] = "";
	int contagem;
	char* sql = "SELECT COUNT(DISTINCT estabelecimento) FROM fichas_docencia ORDER BY estabelecimento";
	PyObject* list = PyList_New(0);
	PyObject* tuplo;
	struct NumberPerYear array[PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList)))];
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		//Creates the path to the databases
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));

		//Open BD, do query and Close BD
		openBD (bd,sql,callbackInt, &contagem);

		//Creates an array of structures to carry out the qsort per Nº of Institutions
		struct NumberPerYear temp;
		temp.year = PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));
		temp.number = contagem;
		array[i] = temp;
	}

	//Organizes the data according to user choice
	//in this case the user may choose to organize NINST (nº of institution)
	//or by year, this is default
	if(strcmp((PyString_AsString(orderBy)),"NINST") == 0)
	{
		qsort(array,PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))),sizeof(struct NumberPerYear), comparenNumberPerYear);
	}

	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		tuplo = PyTuple_New(2);
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(array[i].year));
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyInt_FromLong(array[i].number));
		
		PyList_Append(list, tuplo);//append the organized values on the PyList
	}
	
	return list;	
}

//Return a list with the number of degrees of the years choosen by the user
//used by function nDocentesPerDegreePerYear to know how many memory need to allocate
PyObject* nDegreeYear(PyObject* yearList)
{
	int i = 0;
	char bd[16] = "";
	int contagem;
	char* sql = "SELECT COUNT(DISTINCT grau) FROM fichas_docencia ORDER BY grau";
	PyObject* list = PyList_New(0);
	PyObject* tuplo;
	struct NumberPerYear array[PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList)))];
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		//Creates the path to the databases
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));

		//Open BD, do query and Close BD
		openBD (bd,sql,callbackInt, &contagem);

		//Creates an array of structures to carry out the qsort per Nº of Institutions
		struct NumberPerYear temp;
		temp.year = PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));
		temp.number = contagem;
		array[i] = temp;
	}
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		tuplo = PyTuple_New(2);
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(array[i].year));
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyInt_FromLong(array[i].number));
		
		PyList_Append(list, tuplo);//append the organized values on the PyList
	}
	return list;	
}

//Return a array with the sum of the number of degrees per institutions of the years choosen by the user
//used by the function nDocentesPerDegreePerInstPerYear to know how many memory need to allocate
int* nDegreeInstYear(PyObject* yearList)
{
	int i = 0;
	char bd[16] = "";
	int contagem;
	char* sql = "SELECT sum(totalGraus) FROM (select count(Distinct grau)\
					as totalGraus from fichas_docencia group by estabelecimento order by grau)";
	PyObject* list = PyList_New(0);

	//int array[PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList)))];
	int* array = malloc((PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))))*sizeof(struct nDocentesPerDegreePerInstPerYear));
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		//Creates the path to the databases
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));

		//Open BD, do query and Close BD
		openBD (bd,sql,callbackInt, &contagem);

		//put data in the array
		array[i] = contagem;
	}
	
	return array;	
}

//list of institutions per year without the file write
//return a PyList with the institutions to TeachersHiring Function
PyObject* listInstNoWrite(PyObject* year)
{
	int j = 0;
	char bd[16] = "";
	char* sql = "SELECT DISTINCT estabelecimento FROM fichas_docencia ORDER BY estabelecimento";

	PyObject* list = PyList_New(0);
	
	//Cria o caminho para as bases de dados
	strcpy(bd, bdName(PyInt_AS_LONG( year)));

	//Abrir BD, Efectuar Query e Fechar BD
	openBD (bd,sql,callbackPyList, &list);

	int listSize = PyList_Size(list);

	//create an array with PyObjects
    PyObject **arrayPyObject = malloc( sizeof(PyObject*) * listSize );
		
	//copy the list elements to the array
	for(j = 0; j < listSize; j++)
	{		
		arrayPyObject[j] = PyList_GetItem(list, j);
    }
		
	//order with qsort
	qsort(arrayPyObject, listSize, sizeof(PyObject*), PyStringCmp);
		
	//insert sorted items into pylist again
    for (j = 0; j < listSize; j++)
	{
        PyList_SetItem(list, j, arrayPyObject[j]);
    }
	
	return list;	
}